
<?php

// *************************************************************
// SR Closure rate processing:
// *************************************************************
// TODO: step : BIG report: by engeneer (by product/ Priority)
// TODO: step : Analitiic by all other data.
// TODO: step : different Aggregation levels.
// TODO: by product: select count(sr) as sr_volume, val as product, tse_name from srs, dictionary, tse where id = product and owner_id = owner group by tse_name, val order by tse_name, product;
// retrieving amount of closed srs for each engineer in a team

@ $result = mysql_query('select count(sr) as sr_vol, count(rca) as rca, count(techlead) as techlead, tse_name, team from srs, tse
                where owner_id = owner and
                      owner_id in (' . $ids . ') and
                      closed > ' . $from . ' and
                      closed < ' . $till . '
                group by tse_name
                order by team, sr_vol desc;');

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['closed'] = $res['sr_vol'];
    $forperiod[$res['team']][$res['tse_name']]['rca'] = $res['rca'];
    @ $forperiod[$res['team']][$res['tse_name']]['rcaper'] = sprintf("%.1f", $res['rca'] * 100 / $forperiod[$res['team']][$res['tse_name']]['closed']);
    $forperiod[$res['team']][$res['tse_name']]['techlead'] = $res['techlead'];
    @ $forperiod[$res['team']][$res['tse_name']]['tlaper'] = sprintf("%.1f", $res['techlead'] * 100 / $forperiod[$res['team']][$res['tse_name']]['closed']);
}

// retrieving amount of closed srs for each team
@ $result = mysql_query('select count(sr) as sr_vol, team from srs, tse
                where owner_id = owner and
                      owner_id in (' . $ids . ') and
                      closed > ' . $from . ' and
                      closed < ' . $till . '
                group by team
                order by team, sr_vol desc;');

while (@ $res = mysql_fetch_assoc($result))
    $totalforperiod[$res['team']]['closed'] = $res['sr_vol'];
@ mysql_free_result($result);

// retrieving amount of SRs with Tier-3 hands off for each team
@ $result = mysql_query('select count(sr) as to_t3, team from srs, tse
                where owner_id = owner and
                      owner_id in (' . $ids . ') and
                      closed > ' . $from . ' and
                      closed < ' . $till . ' and
                      tier_t_owner is not null
                group by team
                order by team, to_t3 desc;');

// calculating percentage of T3 hands off
while (@ $res = mysql_fetch_assoc($result)) {
    $totalforperiod[$res['team']]['tier3'] = $res['to_t3'];
    @ $totalforperiod[$res['team']]['tier3per'] = sprintf("%.1f", $res['to_t3'] * 100 / $totalforperiod[$res['team']]['closed']);
}
@ mysql_free_result($result);

// retrieving amount of SRs with Tier-3 hands off
@ $result = mysql_query('select count(sr) as to_t3, tse_name, team from srs, tse
                where owner_id = owner and
                      owner_id in (' . $ids . ') and
                      closed > ' . $from . ' and
                      closed < ' . $till . ' and
                      tier_t_owner is not null
                group by tse_name
                order by team, to_t3 desc;');

// calculating percentage of T3 hands off
while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['tier3'] = $res['to_t3'];
    @ $forperiod[$res['team']][$res['tse_name']]['tier3per'] = sprintf("%.1f", $res['to_t3'] * 100 / $forperiod[$res['team']][$res['tse_name']]['closed']);
}
@ mysql_free_result($result);

//  Retrieving amount of SRs with a useful KCS linked by you document / Linked in SRs closed by other TSE / Closed with a linked KCS (icluding other TSE)
@ $result = mysql_query('
            select team, tse_name, count(distinct kcs.sr) as linked
            from kcs, tse, srs
            where srs.owner = tse.owner_id and
                  kcs.sr = srs.sr and
                  closed >= ' . $from . ' and
                  closed <= ' . $till . ' and
                  srs.owner in (' . $ids . ')
            group by tse_name
            order by team, linked desc;');

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['kcs'] = $res['linked'];
    @ $forperiod[$res['team']][$res['tse_name']]['kcsper'] = sprintf("%.1f", $res['linked'] * 100 / $forperiod[$res['team']][$res['tse_name']]['closed']);
}
@ mysql_free_result($result);

//  Retrieving amount of SRs with a useful KCS linked by TSE document
@ $result = mysql_query('
            select team, count(distinct kcs.sr) as linked
            from kcs, tse, srs
            where owner_id = owner and
                  kcs.sr = srs.sr and
                  closed >= ' . $from . ' and
                  closed <= ' . $till . ' and
                  srs.owner in (' . $ids . ')
            group by team
            order by team, linked desc;');

while (@ $res = mysql_fetch_assoc($result)) {
    $totalforperiod[$res['team']]['kcs'] = $res['linked'];
    @ $totalforperiod[$res['team']]['kcsper'] = sprintf("%.1f", $res['linked'] * 100 / $totalforperiod[$res['team']]['closed']);
}
@ mysql_free_result($result);

// Retreiving amount of Surveys for each team

$query = 'select team, count(sr) as surveys
                      from custsat, tse
                      where
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by team
                      order by team;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $totalforperiod[$res['team']]['surveys'] = $res['surveys'];
}
@ mysql_free_result($result);



// Retrieving Customer Satisfaction average results and TOP marks percentge
$query = 'select team, tse_name, count(sr) as surveys,
                      cast(avg(skill) AS char(4)) as skill,
                      cast(avg(advised) as char(4)) as advised,
                      cast(avg(professional) as char(4)) as prof,
                      cast(avg(time) as char(4)) as time,
                      cast(avg(efect) as char(4)) as efect,
                      cast(avg(overall) as char(4)) as overall
                      from custsat, tse
                      where
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['surveys'] = $res['surveys'];
    $forperiod[$res['team']][$res['tse_name']]['skill'] = $res['skill'];
    $forperiod[$res['team']][$res['tse_name']]['advised'] = $res['advised'];
    $forperiod[$res['team']][$res['tse_name']]['prof'] = $res['prof'];
    $forperiod[$res['team']][$res['tse_name']]['time'] = $res['time'];
    $forperiod[$res['team']][$res['tse_name']]['efect'] = $res['efect'];
    $forperiod[$res['team']][$res['tse_name']]['overall'] = $res['overall'];
}
@ mysql_free_result($result);

// Retrieving topmarks (sum of >3) * 100  / all marks

$query = 'select team, tse_name, count(skill) as skill
                      from custsat, tse
                      where skill > 3 and
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['topmarks'] += $res['skill'];
}
@ mysql_free_result($result);

$query = 'select team, tse_name, count(advised) as advised
                      from custsat, tse
                      where advised > 3 and
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['topmarks'] += $res['advised'];
}
@ mysql_free_result($result);

$query = 'select team, tse_name, count(professional) as prof
                      from custsat, tse
                      where professional > 3 and
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['topmarks'] += $res['prof'];
}

$query = 'select team, tse_name, count(time) as time
                      from custsat, tse
                      where time > 3 and
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['topmarks'] += $res['time'];
}

$query = 'select team, tse_name, count(efect) as efect
                      from custsat, tse
                      where efect > 3 and
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['topmarks'] += $res['efect'];
}

$query = 'select team, tse_name, count(overall) as overall
                      from custsat, tse
                      where overall > 3 and
                      owner_id = sr_owner and
                      survey_date >= ' . $from . ' and
                      survey_date <= ' . $till . ' and
                      sr_owner in (' . $ids . ')
                      group by tse_name
                      order by team, tse_name;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $forperiod[$res['team']][$res['tse_name']]['topmarks'] += $res['overall'];
    @ $forperiod[$res['team']][$res['tse_name']]['topmarks'] = sprintf("%.1f", $forperiod[$res['team']][$res['tse_name']]['topmarks'] * 100 / 6 / $forperiod[$res['team']][$res['tse_name']]['surveys']);

}


mysql_free_result($result);


//echo '<br>all closed in period <pre>';  print_r($productforperiod);  echo '</pre>';
?>
